-- =============================================
-- This procedure takes an original ContentID and ALTER  a copy of it with same attributes.
--
-- Modifications
-- 08/12/2003    E.Tatsui   Created
-- =============================================

CREATE           PROCEDURE [dbo].[amsp_CMCopyContentAsNew]
    @InOriginalContentID numeric,
  @InContactID numeric,
  @InTargetNavMenuID numeric = NULL,
  @OutNewContentID numeric OUTPUT
AS

BEGIN

  DECLARE
    @NavMenuID numeric,
    @OrigURLSafeName varchar(255),
    @URLSafeName varchar(255),
    @Counter smallint

  SELECT @OrigURLSafeName = URLSafeName,
         @NavMenuID = NavMenuID
    FROM Content WITH (NOLOCK)
   WHERE ContentID = @InOriginalContentID

  -- If there is a specific target to copy the content to, use the NavMenuID.
  IF @InTargetNavMenuID IS NOT NULL
    SET @NavMenuID = @InTargetNavMenuID

  EXEC amsp_CMCopyContentRow @InOriginalContentID, @InContactID, @OutNewContentID OUTPUT

  --Make a unique URLSafeName
  EXEC amsp_CMGetUniqueContentName NULL, @OrigURLSafeName, @NavMenuID, @URLSafeName OUTPUT

  -- amsp_CMCopyContentRow creates a Content record as a new version of the existing one,
  -- update fields to make this a new branch.
  UPDATE Content
     SET URLSafeName = @URLSafeName,
         OriginalContentID = NULL,
         PreviousContentID = NULL,
         ExpirationDate = NULL,
         PostFuseURL = NULL,
         PreFuseURL = NULL,
         PublishLocation = NULL,
         ReminderSentDateTime = NULL,
         PublicationDate = NULL,
         SortOrder = (SELECT IsNull(Max(SortOrder),0) + 1
                        FROM Content
                       WHERE NavMenuID = @NavMenuID)
   WHERE ContentID = @OutNewContentID
  
  DELETE FROM Content_Change_Request WHERE ContentID =  @OutNewContentID
END

GO
GRANT EXECUTE ON  [dbo].[amsp_CMCopyContentAsNew] TO [IMIS]
GO